from Mark2Mkt.CalcPnL import *
from DataAccess.DBConnFactory import DBConnFactory
from Misc.Utils import *

from string import Template


def query_exposure(start_date, end_date, tick_list=None, portf_list=None):
	
	#pnl result
	r = calc_pnl(start_date, end_date, tick_list, portf_list)
	
	conn = DBConnFactory().get_db_connection('PKEDB')
	cursor = conn.cursor()	
	#cursor.execute('''CREATE global TEMPORARY TABLE tmp_pnl 
	#		(ticker VARCHAR(20) NOT NULL, 
	#		portfolio_id VARCHAR(16) NOT NULL, 
	#		currency VARCHAR(8), 
	#		pnl number(12,4),
	#		long_pnl number(12,4),
	#		short_pnl number(12,4))''')
	cursor.executemany('''insert into tmp_pnl values(:1,:2,:3,:4,:5,:6)''', r)
	
	sql_tpl = Template('''	
	select dp.lticker sec_ticker,
		s."name" sec_name,
		pnl.long_pnl,
		pnl.short_pnl,
		dp.security_type sec_type,
		pnl pnl_local,
		pnl/fx.price pnl_base,
		pnl/fx.price/pmv.mkt_val contribution,
		p."name" portf_name,
		dp.price_currency
	from daily_position_tick_ex dp
	left join comm_security_static_info s 
		on s.ticker=dp.lticker 
	join portfolio_info p 
		on dp.portfolio_id=p.portfolio_id
	join security_type_rank r 
		on r.sec_type=dp.security_type 
	join tmp_pnl pnl
		on pnl.ticker=dp.ticker
		and pnl.portfolio_id=dp.portfolio_id
	join comm_fx_latest fx 
		on fx.ref_date=TO_DATE('${REF_DATE}','yyyy-mm-dd')
		and fx.dom_curncy=dp.price_currency 
		and fx.for_curncy=p.currency
	join portfolio_mkt_val pmv
		on pmv.ref_date=TO_DATE('${ASSET_VALUATION_DATE}','yyyy-mm-dd')
		and dp.portfolio_id=pmv.portfolio_id
	where dp.ref_date=TO_DATE('${REF_DATE}','yyyy-mm-dd')
	order by dp.portfolio_id,r.rank,dp.lticker
	''')
	
	one_day = timedelta(days=1)	
	asset_valuation_day = start_date - one_day
	sql_text = sql_tpl.substitute(REF_DATE=end_date.isoformat(),
							ASSET_VALUATION_DATE=asset_valuation_day.isoformat())						
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	#conn.commit()
	return r
	